import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self):
        self.conn=pymysql.connect(
            host:='127.0.0.1',
            user= 'root',
            password='root',
            database='scenic',
            port= 3306,
            charset= 'utf8'
        )
    
    def get_scenic_data(self):
        """获取数据
        """

        cursor=self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql="""
        select t.tourist_agency_name, rel.id_no, left(rel.id_no, 2) as province_code,
        DAYOFWEEK(gate.create_time) as non_weekend,
        cast(substring(rel.id_no,7,4)as unsigned)as birth_year 
        from ticket_order_user_rel rel
        left join ticket_order t on t.id = rel.order_id 
        left join order_user_gate_rel gate on gate.ticket_rel_id=rel.id 
        where t.pay_time != '' and t.tourist_agency_name !=''

        """
        cursor.execute(sql)
        ret=cursor.fetchall()

        df=pd.DataFrame(ret)
        #print(df.head)
        #数据清洗
        df['non_weekend']=df['non_weekend'].apply(lambda x: 1 if x not in [1,7] else 0)
        
        #新增有效标记列
        df['valid_id']=df['id_no'].apply(lambda x: 1 if x and str(x).split !='' else 0)

        #判断是否外省游客及老年人游客
        df['out_province_ratio']=df.apply(
            lambda x : 1 if(x['valid_id'] and x['province_code'] !=44)else 0 if x['valid_id'] else np.nan,
            axis=1
        )

        df['elderly_ratio']=df.apply(
            lambda x : 1 if(x['valid_id'] and  2025 - x['birth_year'] >= 60)else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        #print(df)
        #分组聚合计算占比
        result=df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            out_province=('out_province_ratio','sum'),
            elderly=('elderly','sum'),
            non_weekend=('non_weekend','mean'),
        ).reset_index()
        #print(result.head)
        #计算实际比例
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0,np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0,np.nan)

        #清除中间列
        result = result.drop(['out_province_ratio','elderly'])
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        #print(result.head)
        result.to_csv('scenic_data.csv')

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()